package com.ndp.fb.rdb.db.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.ndp.fb.rdb.api.ProductManageRdbService;
import com.ndp.fb.rdb.db.DaoTemplate;
import com.ndp.fb.rdb.db.impl.mapper.DefaultRowMapper;
import com.ndp.fb.rdb.model.Pager;
import com.ndp.fb.rdb.model.PixelId;
import com.ndp.fb.rdb.model.productmanage.FeedWeExt;
import com.ndp.fb.rdb.model.productmanage.ProductManage;
import com.ndp.fb.rdb.model.productmanage.ProductManageCondition;
import com.ndp.fb.rdb.model.productmanage.ProductManageExt;
import com.ndp.fb.util.StringUtil;

import java.util.List;

/**
 * @Author: Evan on 12/30/2015  14:57.
 * Email: evan.wan@ndpmedia.com
 */
@Service
public class ProductManageRdbServiceImpl extends DaoTemplate implements ProductManageRdbService {

    @Override
    public Long save(final ProductManage pm) {
        return saveResultId(pm);
    }

    @Override
    public List<ProductManage> findByUserIdAndName(Long orgId, String name) {
        StringBuffer sql = new StringBuffer("SELECT * FROM product_manage WHERE is_delete = 0 AND org_id = ? ");
        if (StringUtil.isEmpty(name)) {
            return getJdbcTemplate().query(sql.toString(), new Object[]{orgId}, DefaultRowMapper.autoRowMapper(ProductManage.class));
        } else {
            sql.append(" AND product_name LIKE  ? ");
            return getJdbcTemplate().query(sql.toString(), new Object[]{orgId, "%" + name + "%"}, DefaultRowMapper.autoRowMapper(ProductManage.class));
        }
    }


    @Override
    public List<ProductManage> findByProductId(Long proId) {
        String sql = "SELECT * FROM product_manage WHERE product_id = ?";
        return getJdbcTemplate().query(sql.toString(), new Object[]{proId}, DefaultRowMapper.autoRowMapper(ProductManage.class));
    }

    @Override
    public ProductManage findByProductIdAndProductPlatform(Long productId, String productPlatform) {
        String sql = "SELECT * FROM product_manage WHERE product_id = ? AND product_platform = ?";
        return findOne(sql, new Object[]{productId, productPlatform}, DefaultRowMapper.autoRowMapper(ProductManage.class));
    }

    @Override
    public List<ProductManage> findByProductNameAndOrgId(String prodName, Long orgId) {
        String sql = "SELECT * FROM product_manage WHERE product_name = ? AND org_id = ?";
        return getJdbcTemplate().query(sql.toString(), new Object[]{prodName, orgId}, DefaultRowMapper.autoRowMapper(ProductManage.class));
    }

    @Override
    public List<ProductManage> findByOrgId(Long orgId) {
        String sql = "SELECT DISTINCT a.product_id, a.* FROM product_manage a join ad_publish_condition b on a.product_id = b.connection_object_id where b.status =1 and a.is_delete=0 and a.org_id=?";
        return getJdbcTemplate().query(sql.toString(), new Object[]{orgId}, DefaultRowMapper.autoRowMapper(ProductManage.class));
    }

    @Override
    public List<ProductManageExt> findEcCustomersWithFbAccountByUserId(String userId) {
        String sql = "SELECT " +
                " pm.*," +
                " apcda.`fb_account_id`" +
                " FROM" +
                " ad_publish_condition AS apc," +
                " product_manage AS pm," +
                " ad_publish_condition_detail_account AS apcda," +
                " fb_user AS fu," +
                " fb_userpermissions_adaccounts AS fua" +
                " WHERE apc.`connection_object_id` = pm.`product_id`" +
                " AND apc.id = apcda.`condition_id` " +
                " AND fua.`fb_account_id` = apcda.`fb_account_id`" +
                " AND fua.`fb_user_id` = fu.`fb_user_id`" +
                " AND pm.`product_type` = 2 " +
                " AND pm.`user_id` = ?" +
                " AND fu.`user_id` = ?";
        return getJdbcTemplate().query(sql.toString(), new Object[]{userId, userId}, DefaultRowMapper.autoRowMapper(ProductManageExt.class));
    }

    @Override
    public List<Long> findFbAccountIdByUserIdAndProductId(String userId, String productId) {
        String sql = "SELECT " +
                " DISTINCT(apcda.`fb_account_id`) " +
                " FROM" +
                " ad_publish_condition AS apc," +
                " product_manage AS pm," +
                " ad_publish_condition_detail_account AS apcda," +
                " fb_user AS fu," +
                " fb_userpermissions_adaccounts AS fua" +
                " WHERE apc.`connection_object_id` = pm.`product_id`" +
                " AND apc.id = apcda.`condition_id` " +
                " AND fua.`fb_account_id` = apcda.`fb_account_id` " +
                " AND fua.`fb_user_id` = fu.`fb_user_id` " +
                " AND pm.`user_id` = ?" +
                " AND fu.`user_id` = ?" +
                " AND pm.`product_id` = ?";
        return getJdbcTemplate().queryForList(sql.toString(), new Object[]{userId, userId, productId}, Long.class);
    }

    @Override
    public ProductManage findById(Long id) {
        String sql = "select * from product_manage where id = ?";
        return findOne(sql, new Object[]{id}, DefaultRowMapper.autoRowMapper(ProductManage.class));
    }

    @Override
    public ProductManage findByProductId(Long prodId, String userId) {
        String sql = "select * from product_manage where product_id = ? and user_id=?";
        return findOne(sql, new Object[]{prodId, userId}, DefaultRowMapper.autoRowMapper(ProductManage.class));
    }

    @Override
    public PixelId findFbPixelByProductId(Long productId) {
        String sql = "SELECT pid.* FROM `pixel_id` pid INNER JOIN  product_manage pm  on pid.mk_pixel_id = pm.mk_pixel_id where pm.product_id = ?";
        return findOne(sql, new Object[]{productId}, DefaultRowMapper.autoRowMapper(PixelId.class));
    }

    @Override
    public List<ProductManageExt> findEcCustomersByUserId(String userId) {

        String sql = "SELECT pm.*\n" +
                "from product_manage pm\n" +
                "Inner JOIN ad_publish_condition apc on \n" +
                "(apc.`connection_object_id` = pm.`product_id`  AND apc.user_id = ? AND apc.`status` = 1)\n" +
                "where  pm.`product_type` = 2 \n" +
                "ORDER BY pm.product_name";
        return getJdbcTemplate().query(sql, new Object[]{userId}, DefaultRowMapper.autoRowMapper(ProductManageExt.class));
    }

    @Override
    public List<ProductManage> findByUserId(String userId) {
        return null;
    }

    @Override
    public List<ProductManage> findAll() {
        String sql = "select * from product_manage where is_delete=0";
        return getJdbcTemplate().query(sql, DefaultRowMapper.autoRowMapper(ProductManage.class));
    }

    @Override
    public List<ProductManage> findAllByOrgId(Long orgId) {
        String sql = "select * from product_manage where is_delete=0 and org_id = ?";
        return getJdbcTemplate().query(sql, new Object[]{orgId}, DefaultRowMapper.autoRowMapper(ProductManage.class));
    }

    @Override
    public Pager<ProductManageCondition> findByUserIdAndName(Long orgId, String userId, String name, Integer firstResult, Integer pageSize) {
        if (StringUtil.isEmpty(name)) {
            String sql = "SELECT  apc.`status`,apc.operation_status, apc.id as condition_id,pm.*\n" +
                    "from product_manage pm\n" +
                    "LEFT JOIN ad_publish_condition apc on \n" +
                    "(apc.`connection_object_id` = pm.`product_id`  AND apc.user_id = ?)\n" +
                    "where  pm.`product_type` = 2 \n" +
                    " And pm.is_delete=0 " +
                    " AND pm.org_id = ?\n" +
                    "ORDER BY pm.product_name";

            return findPager(sql, new Object[]{userId, orgId}, firstResult, pageSize, DefaultRowMapper.autoRowMapper(ProductManageCondition.class));
        } else {

            String sql = "SELECT  apc.`status`,apc.operation_status, apc.id as condition_id,pm.*\n" +
                    "from product_manage pm\n" +
                    "LEFT JOIN ad_publish_condition apc on \n" +
                    "(apc.`connection_object_id` = pm.`product_id`  AND apc.user_id = ?)\n" +
                    "where  pm.`product_type` = 2 \n" +
                    " And pm.is_delete=0 " +
                    " AND pm.org_id = ?\n" +
                    " AND pm.product_name like ? \n" +
                    "ORDER BY pm.product_name";

            return findPager(sql, new Object[]{userId, orgId, "%" + name + "%"}, firstResult, pageSize, DefaultRowMapper.autoRowMapper(ProductManageCondition.class));
        }
    }

    @Override
    public List<FeedWeExt> findPmAndFeedAllByOrgId(Long orgId) {

        String sql = "SELECT pm.product_id, pm.product_name,pm.product_type,pm.product_platform,pm.mk_pixel_id,pm.product_url,pm.product_icon,pw.*\n" +
                " from product_manage  pm\n" +
                "LEFT JOIN product_we_feed pw on pm.product_id  = pw.product_id and pw.org_id = ? and  pw.is_delete = 0\n" +
                "where  pm.org_id = ? and pm.is_delete = 0";

        return getJdbcTemplate().query(sql, new Object[]{orgId, orgId}, DefaultRowMapper.autoRowMapper(FeedWeExt.class));
    }
}
